package com.ph.irp.base.bom.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.bom.dao.BOMGroupDAO;
import com.ph.irp.model.PBaseBomgroup;
import com.ph.util.DBUtil;

public class BOMGroupDAOImpl implements BOMGroupDAO {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List<PBaseBomgroup> getAllBOMGroup() throws Throwable {
		List<PBaseBomgroup> resList = new ArrayList<PBaseBomgroup>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb
					.append("select bom.BOMGroupID bomID,bom.BOMGroupName groupName,");
			sb.append("bom.BOMGroupCode bomCode,bom.ParentID parentID ");
			sb.append("from p_base_bomgroup bom");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseBomgroup bg = new PBaseBomgroup();

				bg.setBomgroupId(rs.getInt("bomID"));
				bg.setBomgroupName(rs.getString("groupName"));
				bg.setBomgroupCode(rs.getString("bomCode"));
				bg.setParentId(rs.getInt("parentID"));

				resList.add(bg);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return resList;
	}

	public List<PBaseBomgroup> getAllBOMGroupExcludeBOMGroupID(
			String editBOMGroupID) throws Throwable {
		List<PBaseBomgroup> resList = new ArrayList<PBaseBomgroup>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb
					.append("select bom.BOMGroupID bomID,bom.BOMGroupName groupName,");
			sb.append("bom.BOMGroupCode bomCode,bom.ParentID parentID ");
			sb.append("from p_base_bomgroup bom ");
			sb.append("where BOMGroupID!=").append(editBOMGroupID);
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseBomgroup bg = new PBaseBomgroup();

				bg.setBomgroupId(rs.getInt("bomID"));
				bg.setBomgroupName(rs.getString("groupName"));
				bg.setBomgroupCode(rs.getString("bomCode"));
				bg.setParentId(rs.getInt("parentID"));

				resList.add(bg);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return resList;

	}

	public String getBOMGroupNameByBOMGroupID(int groupID) throws Throwable {
		String res = null;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select bom.BOMGroupName groupName ");
			sb.append("from p_base_bomgroup bom ");
			sb.append("where BOMGroupID=").append(groupID);
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				res = rs.getString("groupName");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return res;

	}

	public List<PBaseBomgroup> getBOMGroupByBOMParentGroupId(int groupID)
			throws Throwable {
		List<PBaseBomgroup> resList = new ArrayList<PBaseBomgroup>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select bom.BOMGroupName groupName ");
			sb.append("from p_base_bomgroup bom ");
			sb.append("where ParentID=").append(groupID);
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseBomgroup bomGroup = new PBaseBomgroup();
				bomGroup.setBomgroupName(rs.getString("groupName"));
				resList.add(bomGroup);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return resList;
	}

	public List<PBaseBomgroup> getBOMGroupByCodeAndName(String code, String name)
			throws Throwable {
		List<PBaseBomgroup> resList = new ArrayList<PBaseBomgroup>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb
					.append("select bom.BOMGroupID bomID,bom.BOMGroupName groupName,");
			sb.append("bom.BOMGroupCode bomCode,bom.ParentID parentID ");
			sb.append("from p_base_bomgroup bom ");
			sb.append("where BOMGroupCode like '%").append(code).append("%' ");
			sb.append("and BOMGroupName like '%").append(name).append("%' ");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseBomgroup bg = new PBaseBomgroup();

				bg.setBomgroupId(rs.getInt("bomID"));
				bg.setBomgroupName(rs.getString("groupName"));
				bg.setBomgroupCode(rs.getString("bomCode"));
				bg.setParentId(rs.getInt("parentID"));

				resList.add(bg);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return resList;

	}

	public void addBOMGroup(String bomGroupCode, String bomGroupName,
			String bomGroupParentID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb
					.append(" insert into p_base_bomgroup(BOMGroupName,BOMGroupCode,ParentID)");
			sb.append(" values('");
			sb.append(bomGroupName);
			sb.append("','").append(bomGroupCode);
			sb.append("',").append(Integer.parseInt(bomGroupParentID));
			sb.append(")");

			// connection.insert(sb.toString());
			// dataSource.set
			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

	}

	public PBaseBomgroup getBOMGroupByBOMGroupId(String BOMGroupID)
			throws Throwable {
		PBaseBomgroup bg = new PBaseBomgroup();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb
					.append("select bom.BOMGroupID bomID,bom.BOMGroupName groupName,");
			sb.append("bom.BOMGroupCode bomCode,bom.ParentID parentID ");
			sb.append("from p_base_bomgroup bom ");
			sb.append("where BOMGroupID =")
					.append(Integer.parseInt(BOMGroupID));

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				bg.setBomgroupId(rs.getInt("bomID"));
				bg.setBomgroupName(rs.getString("groupName"));
				bg.setBomgroupCode(rs.getString("bomCode"));
				bg.setParentId(rs.getInt("parentID"));

			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return bg;
	}

	public void editBOMGroup(PBaseBomgroup editBOMGroup) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("update p_base_bomgroup set BOMGroupName='");
			sb.append(editBOMGroup.getBomgroupName())
					.append("',BOMGroupCode='");
			sb.append(editBOMGroup.getBomgroupCode()).append("',ParentID=");
			sb.append(editBOMGroup.getParentId());
			sb.append(" where BOMGroupID=")
					.append(editBOMGroup.getBomgroupId());

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

	}

	public void deleteBOMGroupByBOMGroupIDs(String deletedBOMGroupID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;

		try {
			StringBuilder sb = new StringBuilder();
			sb.append(" delete from p_base_bomgroup ");
			sb.append(" where BOMGroupID in (").append(deletedBOMGroupID)
					.append(")");

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
	}

	public void deleteBOMGroupByBOMGroupID(String deletedBOMGroupID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;

		try {
			StringBuilder sb = new StringBuilder();
			sb.append(" delete from p_base_bomgroup ");
			sb.append(" where BOMGroupID =").append(deletedBOMGroupID);

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
	}
}
